#region Member Variables
private StringCollection _droppedProcedureNames = new StringCollection();
private StringCollection _generatedProcedureNames = new StringCollection();
#endregion

#region Isolation Level
public enum TransactionIsolationLevelEnum
{
	ReadCommitted,
	ReadUncommitted,
	RepeatableRead,
	Serializable
}

public void GenerateSetTransactionIsolationLevelStatement(TransactionIsolationLevelEnum isolationLevel)
{
	Response.Write("SET TRANSACTION ISOLATION LEVEL ");
	
	switch (isolationLevel)
	{
		case TransactionIsolationLevelEnum.ReadUncommitted:
		{
			Response.WriteLine("READ UNCOMMITTED");
			break;
		}
		case TransactionIsolationLevelEnum.RepeatableRead:
		{
			Response.WriteLine("REPEATABLE READ");
			break;
		}
		case TransactionIsolationLevelEnum.Serializable:
		{
			Response.WriteLine("SERIALIZABLE");
			break;
		}
		default:
		{
			Response.WriteLine("READ COMMITTED");
			break;
		}
	}
}
#endregion

#region Code Generation Helpers
public string GetTableOwner()
{
	return GetTableOwner(true);
}

public string GetTableOwner(bool includeDot)
{
	if (SourceTable.Owner.Length > 0)
	{
		if (includeDot)
		{
			return "[" + SourceTable.Owner + "].";
		}
		else
		{
			return "[" + SourceTable.Owner + "]";
		}
	}
	else
	{
		return "";
	}
}

public void GenerateDropStatement(string procedureName)
{
	// check to see if this procedure has already been dropped.
	if (!_droppedProcedureNames.Contains(procedureName))
	{
		Response.WriteLine("IF OBJECT_ID(N'{0}') IS NOT NULL", procedureName);
		GenerateIndent(1);
		Response.WriteLine("DROP PROCEDURE {0}", procedureName);
		Response.WriteLine("");
		
		// add this procedure to the list of dropped procedures
		_droppedProcedureNames.Add(procedureName);
	}
}

public void GenerateProcedureHeader(string procedureName)
{
	Response.WriteLine("--region {0}", procedureName);
	Response.WriteLine("");
	Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");
	Response.WriteLine("-- Generated By:   {0} using CodeSmith {1}", System.Environment.UserName, typeof(CodeTemplate).Assembly.GetName().Version.ToString());
	Response.WriteLine("-- Template:       {0}", this.CodeTemplateInfo.FileName);
	Response.WriteLine("-- Procedure Name: {0}", procedureName);
	Response.WriteLine("-- Date Generated: {0}", DateTime.Now.ToLongDateString());
	Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");
}

public void GenerateProcedureFooter(string procedureName)
{
	Response.WriteLine("--endregion");
	Response.WriteLine("");
	Response.WriteLine("GO");
	Response.WriteLine("");
}

public void GenerateIndent(int indentLevel)
{
    for (int i = 0; i < indentLevel; i++)
	{
		Response.Write('\t');
	}
}

public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
	GenerateParameter(column, indentLevel, isFirst, isLast, false);
}

public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool isOutput)
{
	GenerateIndent(indentLevel);
	Response.Write(GetSqlParameterStatement(column, isOutput));
	if (!isLast) Response.Write(",");
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateParameters(ColumnSchemaCollection columns, int indentLevel)
{
	GenerateParameters(columns, indentLevel, false);
}

public void GenerateParameters(ColumnSchemaCollection columns, int indentLevel, bool includeTrailingComma)
{
	ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateParameter(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1 && !includeTrailingComma);
	}
}

public void GenerateColumn(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
	GenerateIndent(indentLevel);
	Response.Write("[");
	Response.Write(column.Name);
	Response.Write("]");
	if (!isLast) Response.Write(",");
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateColumns(ColumnSchemaCollection columns, int indentLevel)
{
	ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateColumn(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
	}
}

public void GenerateUpdate(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
	GenerateIndent(indentLevel);
	Response.Write("[");
	Response.Write(column.Name);
	Response.Write("] = @");
	Response.Write(column.Name);
	if (!isLast) Response.Write(",");
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateUpdates(ColumnSchemaCollection columns, int indentLevel)
{
	ColumnSchemaCollection filteredColumns = FilterReadOnlyAndExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateUpdate(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
	}
}

public void GenerateCondition(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
	GenerateIndent(indentLevel);
	if (!isFirst) Response.Write("AND ");
	Response.Write("[");
	Response.Write(column.Name);
	Response.Write("] = @");
	Response.Write(column.Name);
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateConditions(ColumnSchemaCollection columns, int indentLevel)
{
	ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateCondition(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
	}
}
// 以下2005-09-01 添加，用于创建where 从句中的like 
public void GenerateCondition(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool isInexact)
{
	GenerateIndent(indentLevel);
	if (!isFirst) Response.Write("AND ");
	if (!isInexact)
	{
		Response.Write("[");
		Response.Write(column.Name);
		Response.Write("] = @");
		Response.Write(column.Name);
	}
	else
	{
		Response.Write("[");
		Response.Write(column.Name);
		Response.Write("] LIKE '%' + @");
		Response.Write(column.Name);
		Response.Write(" + '%'");
	}
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateConditions(ColumnSchemaCollection columns, int indentLevel, bool isInexact)
{
	ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateCondition(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1, isInexact);
	}
}
// 以上2005-09-01 添加，用于创建where 从句中的like 

public void GenerateVariable(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
	GenerateIndent(indentLevel);
	Response.Write("@");
	Response.Write(column.Name);
	if (!isLast) Response.Write(",");
	if (indentLevel >= 0)
	{
		Response.WriteLine("");
	}
	else if (!isLast)
	{
		Response.Write(" ");
	}
}

public void GenerateVariables(ColumnSchemaCollection columns, int indentLevel)
{
	ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
	for (int i = 0; i < filteredColumns.Count; i++)
	{
		GenerateVariable(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
	}
}

public void GenerateOrderByClause()
{
	if (OrderByExpression != null && OrderByExpression.Trim().Length > 0)
	{
		Response.WriteLine("ORDER BY");
		GenerateIndent(1);
		Response.WriteLine(OrderByExpression);
	}
}

public ColumnSchemaCollection FilterReadOnlyColumns(ColumnSchemaCollection columns)
{
	ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
	
	for (int i = 0; i < columns.Count; i++)
	{
		if (!ColumnIsReadOnly(columns[i])) filteredColumns.Add(columns[i]);
	}
	
	return filteredColumns;
}

public ColumnSchemaCollection FilterExcludedColumns(ColumnSchemaCollection columns)
{
	ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
	
	for (int i = 0; i < columns.Count; i++)
	{
		if (!ColumnIsExcluded(columns[i])) filteredColumns.Add(columns[i]);
	}
	
	return filteredColumns;
}

public ColumnSchemaCollection FilterReadOnlyAndExcludedColumns(ColumnSchemaCollection columns)
{
	ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
	
	for (int i = 0; i < columns.Count; i++)
	{
		if (!ColumnIsExcludedOrReadOnly(columns[i])) filteredColumns.Add(columns[i]);
	}
	
	return filteredColumns;
}

private Regex excludedColumnRegex = null;

public bool ColumnIsExcluded(ColumnSchema column)
{
	if (column.IsPrimaryKeyMember) return false;
	
	if (excludedColumnRegex == null)
	{
		if (ExcludedColumns != null && ExcludedColumns.Count > 0)
		{
			string excluded = String.Empty;
			for (int i = 0; i < ExcludedColumns.Count; i++)
			{
				if (ExcludedColumns[i].Trim().Length > 0)
				{
					excluded += "(" + Regex.Escape(ExcludedColumns[i]).Replace("\\*", ".*?") + ")|";
				}
			}
			
			if (excluded.Length > 0)
			{
				excluded = excluded.Substring(0, excluded.Length - 1);
				excludedColumnRegex = new Regex(excluded, RegexOptions.IgnoreCase);
			}
		}
	}
	
	if (excludedColumnRegex != null && excludedColumnRegex.IsMatch(column.Name)) return true;
	
	return false;
}

private Regex readOnlyColumnRegex = null;

public bool ColumnIsReadOnly(ColumnSchema column)
{
	if (column.IsPrimaryKeyMember) return false;
	
	if (readOnlyColumnRegex == null)
	{
		if (ReadOnlyColumns != null && ReadOnlyColumns.Count > 0)
		{
			string readOnly = String.Empty;
			for (int i = 0; i < ReadOnlyColumns.Count; i++)
			{
				if (ReadOnlyColumns[i].Trim().Length > 0)
				{
					readOnly += "(" + Regex.Escape(ReadOnlyColumns[i]).Replace("\\*", ".*?") + ")|";
				}
			}
			
			if (readOnly.Length > 0)
			{
				readOnly = readOnly.Substring(0, readOnly.Length - 1);
				readOnlyColumnRegex = new Regex(readOnly, RegexOptions.IgnoreCase);
			}
		}
	}
	
	if (readOnlyColumnRegex != null && readOnlyColumnRegex.IsMatch(column.Name)) return true;
	
	return false;
}

public bool ColumnIsExcludedOrReadOnly(ColumnSchema column)
{
	return ColumnIsExcluded(column) || ColumnIsReadOnly(column);
}
#endregion

#region Procedure Naming
public string GetInsertProcedureName()
{
	return String.Format("{0}[{1}Insert{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}

public string GetUpdateProcedureName()
{
	return String.Format("{0}[{1}Update{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}

public string GetInsertUpdateProcedureName()
{
	return String.Format("{0}[{1}InsertUpdate{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}

public string GetDeleteProcedureName()
{
	return String.Format("{0}[{1}Delete{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}

public string GetSelectProcedureName()
{
	return String.Format("{0}[{1}Get{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}

public string GetSelectAllProcedureName()
{
	return String.Format("{0}[{1}Get{2}All]", GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

public string GetSelectPagedProcedureName()
{
	return String.Format("{0}[{1}Get{2}Paged]", GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

public string GetSelectDetailProcedureName(int i)
{
	return String.Format("{0}[{1}Get{2}By{3}]", GetTableOwner(), ProcedurePrefix, (DetailTable(SourceTable, i)).Name, PrimaryKeyString(SourceTable));
}

public string GetSelectMasterProcedureName(int i)
{
	return String.Format("{0}[{1}Get{2}By{3}]", GetTableOwner(), ProcedurePrefix, (MasterTable(SourceTable, i)).Name, MasterKeyString(SourceTable, i));
}

public string GetSelectByProcedureName(ColumnSchemaCollection targetColumns)
{
	return String.Format("{0}[{1}Get{2}By{3}]", GetTableOwner(), ProcedurePrefix, GetEntityName(true), GetBySuffix(targetColumns));
}

public string GetSelectDynamicProcedureName()
{
	return String.Format("{0}[{1}Get{2}Dynamic]", GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

public string GetDeleteByProcedureName(ColumnSchemaCollection targetColumns)
{
	return String.Format("{0}[{1}Delete{2}By{3}]", GetTableOwner(), ProcedurePrefix, GetEntityName(true), GetBySuffix(targetColumns));
}

public string GetDeleteDynamicProcedureName()
{
	return String.Format("{0}[{1}Delete{2}Dynamic]", GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

public string GetEntityName(bool plural)
{
	string entityName = SourceTable.Name;
	
	if (entityName.StartsWith(TablePrefix))
	{
		entityName = entityName.Substring(TablePrefix.Length);
	}
	
	if (plural)
	{
		entityName = StringUtility.ToPlural(entityName);
	}
	else
	{
		entityName = StringUtility.ToSingular(entityName);
	}
	
	return entityName;
}

public string GetBySuffix(ColumnSchemaCollection columns)
{
    System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
	for (int i = 0; i < columns.Count; i++)
	{
	    if (i > 0) bySuffix.Append("And");
	    bySuffix.Append(columns[i].Name);
	}
	
	return bySuffix.ToString();
}
#endregion

#region Template Overrides
// Assign an appropriate file name to the output.
public override string GetFileName()
{
	if (this.SourceTable != null)
	{
		return this.SourceTable.Name + "_Procedures.sql";
	}
	else
	{
		return base.GetFileName();
	}
}

// Override the OutputFile property and assign our specific settings to it.
[Category("2. Options")]
[FileDialog(FileDialogType.Save, Title="Select Output File", Filter="Query Files (*.sql)|*.sql|All Files (*.*)|*.*", DefaultExtension=".sql")]
public override string OutputFile
{
	get {return base.OutputFile;}
	set {base.OutputFile = value;}
}

protected override void OnPostRender(string result) 
{
	if (this.AutoExecuteScript)
	{
		// execute the output on the same database as the source table.
		CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.SourceTable.Database.ConnectionString, result, new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage)); 
		Trace.Write(scriptResult.ToString());
	}
	
	base.OnPostRender(result);
}

private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
	Trace.WriteLine(e.Message);
}
#endregion

#region Relationship
public string PrimaryKeyString(TableSchema table)
{
	string s = "";
	if(table.HasPrimaryKey)
	{
		for(int i = 0; i < table.PrimaryKey.MemberColumns.Count; i++) 
		{ 
			s = s + table.PrimaryKey.MemberColumns[i].Name;
		}
	}
	return s;
}
public TableSchema DetailTable(TableSchema table, int i)
{
	if(table.PrimaryKeys.Count > 0)
	{
		return table.PrimaryKeys[i].ForeignKeyTable;
	}
	else
	{
		return null;
	}
}
public TableSchema MasterTable(TableSchema table, int i)
{
	if(table.ForeignKeys.Count > 0)
	{
		return table.ForeignKeys[i].PrimaryKeyTable;
	}
	else
	{
		return null;
	}
}
public string MasterKeyString(TableSchema table, int i)
{
	string s = "";
	if(table.HasPrimaryKey && table.ForeignKeys.Count > 0)
	{
		s = table.ForeignKeys[i].ForeignKeyMemberColumns[0].Name;
	}
	return s;
}
#endregion
